package com.mulong.mall.service.support;

import java.math.BigDecimal;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import com.mulong.common.dao.mall.BrandDao;
import com.mulong.common.dao.mall.ProductDao;
import com.mulong.common.dao.mall.SizeConversionDao;
import com.mulong.common.domain.pojo.mall.Brand;
import com.mulong.common.domain.pojo.mall.Product;
import com.mulong.common.domain.pojo.mall.ProductBarcode;
import com.mulong.common.domain.pojo.mall.SizeConversion;
import com.mulong.common.domain.pojo.mall.SupplierChannel;
import com.mulong.common.domain.pojo.mall.custom.BrandQuery;
import com.mulong.common.domain.pojo.mall.custom.DownloadInventory;
import com.mulong.common.enums.GoodsCategory;
import com.mulong.common.enums.GoodsSex;
import com.mulong.common.exception.ErrorEnums;
import com.mulong.common.exception.MulongException;
import com.mulong.common.util.MulongUtil;
import com.mulong.common.util.SizeConverter;
import com.mulong.mall.domain.bo.manager.UploadBarcodeRecord;
import com.mulong.mall.domain.bo.manager.UploadProductRecord;
import com.mulong.mall.domain.bo.manager.UploadSizeConversionRecord;
import com.mulong.mall.util.MallRequestContext;

import cn.hutool.poi.excel.ExcelReader;
import lombok.extern.slf4j.Slf4j;

/**
 * ManagerGoodsSupport
 * 
 * @author mulong
 * @data 2021-06-30 22:18:11
 */
@Slf4j
@Component
public class ManagerGoodsSupport {
    @Autowired
    private ProductDao productDao;
    @Autowired
    private BrandDao brandDao;
    @Autowired
    private SizeConversionDao sizeConversionDao;
    @Autowired
    private MallSupport mallSupport;

    public void addRow(HSSFRow row, DownloadInventory productInventoryRecord, HSSFCellStyle defaultStyle) {
        // 货号（型号）
        HSSFCell skuCell = row.createCell(0);
        skuCell.setCellStyle(defaultStyle);
        skuCell.setCellValue(productInventoryRecord.getSku());
        // 品牌
        HSSFCell brandNameCell = row.createCell(1);
        brandNameCell.setCellStyle(defaultStyle);
        brandNameCell.setCellValue(productInventoryRecord.getBrandShortName());
        // 产品名称
        HSSFCell productNameCell = row.createCell(2);
        productNameCell.setCellStyle(defaultStyle);
        productNameCell.setCellValue(productInventoryRecord.getProductName());
        // 系列
        HSSFCell productSeriesCell = row.createCell(3);
        productSeriesCell.setCellStyle(defaultStyle);
        productSeriesCell.setCellValue(productInventoryRecord.getProductSeries());
        // 大类
        HSSFCell goodsCategoryCell = row.createCell(4);
        goodsCategoryCell.setCellStyle(defaultStyle);
        GoodsCategory goodsCategory = GoodsCategory.getByCode(productInventoryRecord.getGoodsCategory());
        goodsCategoryCell.setCellValue(goodsCategory == null ? null : goodsCategory.getName());
        // 年份
        HSSFCell yearCell = row.createCell(5);
        yearCell.setCellStyle(defaultStyle);
        yearCell.setCellValue(productInventoryRecord.getYear());
        // 季节
        HSSFCell seasonCell = row.createCell(6);
        seasonCell.setCellStyle(defaultStyle);
        seasonCell.setCellValue(productInventoryRecord.getSeason());
        // 性别
        HSSFCell goodsSexCell = row.createCell(7);
        goodsSexCell.setCellStyle(defaultStyle);
        GoodsSex goodsSex = GoodsSex.getByCode(productInventoryRecord.getGoodsSex());
        goodsSexCell.setCellValue(goodsSex == null ? null : goodsSex.getName());
        // 颜色
        HSSFCell colorCell = row.createCell(8);
        colorCell.setCellStyle(defaultStyle);
        colorCell.setCellValue(productInventoryRecord.getColor());
        // 尺码（尺寸、规格）
        HSSFCell normalizedSizeCell = row.createCell(9);
        normalizedSizeCell.setCellStyle(defaultStyle);
        normalizedSizeCell.setCellValue(productInventoryRecord.getNormalizedSize());
        // 重量
        HSSFCell weightCell = row.createCell(10);
        weightCell.setCellStyle(defaultStyle);
        weightCell.setCellValue(productInventoryRecord.getWeight() == null ? null : productInventoryRecord.getWeight().toString());
        // 吊牌价
        HSSFCell productMarketPriceCell = row.createCell(11);
        productMarketPriceCell.setCellStyle(defaultStyle);
        productMarketPriceCell.setCellValue(productInventoryRecord.getProductMarketPrice() == null ? null : productInventoryRecord.getProductMarketPrice().toString());
        // 数量
        HSSFCell qtyCell = row.createCell(12);
        qtyCell.setCellStyle(defaultStyle);
        qtyCell.setCellValue(productInventoryRecord.getQty());
        // 卖点
        HSSFCell salePropsCell = row.createCell(14);
        salePropsCell.setCellStyle(defaultStyle);
        salePropsCell.setCellValue(productInventoryRecord.getSaleProps());
        // 材质
        HSSFCell materialCell = row.createCell(15);
        materialCell.setCellStyle(defaultStyle);
        materialCell.setCellValue(productInventoryRecord.getMaterial());
        // 细节备注
        HSSFCell remarkCell = row.createCell(16);
        remarkCell.setCellStyle(defaultStyle);
        remarkCell.setCellValue(productInventoryRecord.getRemark());
        // 原尺码
        HSSFCell originalSizeCell = row.createCell(17);
        originalSizeCell.setCellStyle(defaultStyle);
        originalSizeCell.setCellValue(productInventoryRecord.getOriginalSize());
        // 销售折扣
        HSSFCell salesDiscountCell = row.createCell(18);
        salesDiscountCell.setCellStyle(defaultStyle);
        salesDiscountCell.setCellValue(productInventoryRecord.getSalesDiscount() == null ? null : productInventoryRecord.getSalesDiscount().toString());
        // 成本折扣
        HSSFCell supplierDiscountCell = row.createCell(19);
        supplierDiscountCell.setCellStyle(defaultStyle);
        supplierDiscountCell.setCellValue(productInventoryRecord.getSupplierDiscount() == null ? null : productInventoryRecord.getSupplierDiscount().toString());
        // 仓库（渠道）名称
        HSSFCell supplierChannelNameCell = row.createCell(20);
        supplierChannelNameCell.setCellStyle(defaultStyle);
        supplierChannelNameCell.setCellValue(productInventoryRecord.getSupplierChannelShortName());
    }

    public List<com.mulong.common.domain.pojo.mall.Inventory> buildInventoryList(
            ExcelReader reader, SupplierChannel supplierChannel) {
        int rowCount = reader.getRowCount();
        if (rowCount <= 1) {
            log.error("upload file is empty");
            throw new MulongException(ErrorEnums.UPLOAD_FILE_EMPTY);
        }
        SizeConverter sizeConverter = mallSupport.getSizeConverter();
        List<com.mulong.common.domain.pojo.mall.Inventory> inventoryRecords = new ArrayList<>(rowCount - 1);
        for (int y = 1; y < rowCount; ++y) {
            com.mulong.common.domain.pojo.mall.Inventory inventory = buildInventory(reader, y, supplierChannel, sizeConverter);
            if (inventory != null) {
                inventoryRecords.add(inventory);
            }
        }
        return inventoryRecords;
    }

    private com.mulong.common.domain.pojo.mall.Inventory buildInventory(
            ExcelReader reader, int rowIndex, SupplierChannel supplierChannel, SizeConverter sizeConverter) {
        Cell skuCell = reader.getCell(0, rowIndex);
        Cell originalSizeCell = reader.getCell(1, rowIndex);
        Cell qtyCell = reader.getCell(2, rowIndex);
        Cell marketPriceCell = reader.getCell(3, rowIndex);
        Cell discountCell = reader.getCell(4, rowIndex);
        String sku = MulongUtil.getStringCellValue(skuCell);
        if (StringUtils.isBlank(sku)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写货号的记录"));
        }
        String originalSize = MulongUtil.getStringCellValue(originalSizeCell);
        if (StringUtils.isBlank(originalSize)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写尺码的记录"));
        }
        String qtyCellValue = MulongUtil.getStringCellValue(qtyCell);
        if (StringUtils.isBlank(qtyCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写数量的记录"));
        }
        Integer qty = Integer.parseInt(qtyCellValue);
        String marketPriceCellValue = MulongUtil.getStringCellValue(marketPriceCell);
        if (StringUtils.isBlank(marketPriceCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写吊牌价的记录"));
        }
        BigDecimal marketPrice = new BigDecimal(marketPriceCellValue);
        String discountCellValue = MulongUtil.getStringCellValue(discountCell);
        if (StringUtils.isBlank(discountCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写成本折扣的记录"));
        }
        BigDecimal discount = new BigDecimal(discountCellValue);
        com.mulong.common.domain.pojo.mall.Inventory record = new com.mulong.common.domain.pojo.mall.Inventory();
        record.setSupplierChannelId(supplierChannel.getId());
        record.setSku(sku);
        record.setOriginalSize(originalSize);
        record.setQty(qty);
        record.setMarketPrice(marketPrice);
        record.setDiscount(discount);
        richInventory(record, sizeConverter);
        return record;
    }

    public void richInventory(com.mulong.common.domain.pojo.mall.Inventory record) {
        SizeConverter sizeConverter = mallSupport.getSizeConverter();
        richInventory(record, sizeConverter);
    }

    private void richInventory(com.mulong.common.domain.pojo.mall.Inventory record, SizeConverter sizeConverter) {
        String sku = record.getSku();
        if (StringUtils.isBlank(sku)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写货号的记录"));
        }
        String originalSize = record.getOriginalSize();
        if (StringUtils.isBlank(originalSize)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写尺码的记录"));
        }
        Product product = productDao.queryBySku(sku);
        if (product == null) {
            return;
        }
        String normalizedSize = sizeConverter.getNormalizedSize(product.getBrandId(), product.getGoodsCategory(), product.getGoodsSex(), originalSize);
        if (StringUtils.isBlank(normalizedSize)) {
            return;
        }
        record.setNormalizedSize(normalizedSize);
    }

    public void addRow(HSSFRow row, com.mulong.common.domain.pojo.mall.custom.CustomProduct record, HSSFCellStyle defaultStyle) {
        // 货号（型号）
        HSSFCell skuCell = row.createCell(0);
        skuCell.setCellStyle(defaultStyle);
        skuCell.setCellValue(record.getSku());
        // 品牌
        HSSFCell brandCell = row.createCell(1);
        brandCell.setCellStyle(defaultStyle);
        brandCell.setCellValue(record.getBrandName());
        // 产品名称
        HSSFCell nameCell = row.createCell(2);
        nameCell.setCellStyle(defaultStyle);
        nameCell.setCellValue(record.getName());
        // 大类
        HSSFCell goodsCategoryCell = row.createCell(3);
        goodsCategoryCell.setCellStyle(defaultStyle);
        GoodsCategory goodsCategory = GoodsCategory.getByCode(record.getGoodsCategory());
        goodsCategoryCell.setCellValue(goodsCategory == null ? null : goodsCategory.getName());
        // 中类
        HSSFCell seriesCell = row.createCell(4);
        seriesCell.setCellStyle(defaultStyle);
        seriesCell.setCellValue(record.getSeries());
        // 年份
        HSSFCell yearCell = row.createCell(5);
        yearCell.setCellStyle(defaultStyle);
        yearCell.setCellValue(record.getYear());
        // 季节
        HSSFCell seasonCell = row.createCell(6);
        seasonCell.setCellStyle(defaultStyle);
        seasonCell.setCellValue(record.getSeason());
        // 性别
        HSSFCell goodsSexCell = row.createCell(7);
        goodsSexCell.setCellStyle(defaultStyle);
        GoodsSex goodsSex = GoodsSex.getByCode(record.getGoodsSex());
        goodsSexCell.setCellValue(goodsSex == null ? null : goodsSex.getName());
        // 颜色
        HSSFCell colorCell = row.createCell(8);
        colorCell.setCellStyle(defaultStyle);
        colorCell.setCellValue(record.getColor());
        // 重量
        HSSFCell weightCell = row.createCell(9);
        weightCell.setCellStyle(defaultStyle);
        weightCell.setCellValue(record.getWeight() == null ? null : record.getWeight().toString());
        // 吊牌价
        HSSFCell marketPriceCell = row.createCell(10);
        marketPriceCell.setCellStyle(defaultStyle);
        marketPriceCell.setCellValue(record.getMarketPrice() == null ? null : record.getMarketPrice().toString());
        // 卖点
        HSSFCell salePropsCell = row.createCell(11);
        salePropsCell.setCellStyle(defaultStyle);
        salePropsCell.setCellValue(record.getSaleProps());
        // 材质
        HSSFCell materialCell = row.createCell(12);
        materialCell.setCellStyle(defaultStyle);
        materialCell.setCellValue(record.getMaterial());
        // 细节备注
        HSSFCell remarkCell = row.createCell(13);
        remarkCell.setCellStyle(defaultStyle);
        remarkCell.setCellValue(record.getRemark());
    }

    public List<UploadProductRecord> buildUploadProductRecordList(ExcelReader reader) {
        int rowCount = reader.getRowCount();
        if (rowCount <= 1) {
            log.error("upload file is empty");
            throw new MulongException(ErrorEnums.UPLOAD_FILE_EMPTY);
        }
        List<UploadProductRecord> uploadRecords = new ArrayList<>(rowCount - 1);
        for (int y = 1; y < reader.getRowCount(); ++y) {
            UploadProductRecord uploadRecord = buildUploadProductRecord(reader, y);
            if (uploadRecord != null) {
                uploadRecords.add(uploadRecord);
            }
        }
        List<String> distinctBrandName = uploadRecords.stream().map(UploadProductRecord::getBrandValue).distinct().collect(Collectors.toList());
        BrandQuery brandQuery = new BrandQuery();
        brandQuery.setNameList(distinctBrandName);
        List<Brand> brands = brandDao.query(brandQuery);
        if (CollectionUtils.isEmpty(brands)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("所有品牌均未识别"));
        }
        Map<String, Brand> brandMap = brands.stream().collect(Collectors.toMap(Brand::getName, Function.identity()));
        for (UploadProductRecord item : uploadRecords) {
            String brandValue = item.getBrandValue();
            Brand brand = brandMap.get(brandValue);
            if (brand == null) {
                throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("有未识别的品牌[" + brandValue + "]"));
            }
            item.setBrand(brand);
        }
        return uploadRecords;
    }

    private UploadProductRecord buildUploadProductRecord(ExcelReader reader, int rowIndex) {
        Cell skuCell = reader.getCell(0, rowIndex);
        Cell brandCell = reader.getCell(1, rowIndex);
        Cell nameCell = reader.getCell(2, rowIndex);
        Cell goodsCategoryCell = reader.getCell(3, rowIndex);
        Cell seriesCell = reader.getCell(4, rowIndex);
        Cell yearCell = reader.getCell(5, rowIndex);
        Cell seasonCell = reader.getCell(6, rowIndex);
        Cell goodsSexCell = reader.getCell(7, rowIndex);
        Cell colorCell = reader.getCell(8, rowIndex);
        Cell weightCell = reader.getCell(9, rowIndex);
        Cell marketPriceCell = reader.getCell(10, rowIndex);
        Cell salePropsCell = reader.getCell(11, rowIndex);
        Cell materialCell = reader.getCell(12, rowIndex);
        Cell remarkCell = reader.getCell(13, rowIndex);
        String sku = MulongUtil.getStringCellValue(skuCell);
        if (StringUtils.isBlank(sku)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写货号的记录"));
        }
        String brandCellValue = MulongUtil.getStringCellValue(brandCell);
        if (StringUtils.isBlank(brandCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写品牌的记录"));
        }
        String name = MulongUtil.getStringCellValue(nameCell);
        String goodsCategoryCellValue = MulongUtil.getStringCellValue(goodsCategoryCell);
        GoodsCategory goodsCategory = GoodsCategory.getByName(goodsCategoryCellValue);
        if (goodsCategory == null) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("有未识别的大类[" + goodsCategoryCellValue + "]"));
        }
        String series = MulongUtil.getStringCellValue(seriesCell);
        String year = MulongUtil.getStringCellValue(yearCell);
        String season = MulongUtil.getStringCellValue(seasonCell);
        String goodsSexCellValue = MulongUtil.getStringCellValue(goodsSexCell);
        GoodsSex goodsSex = GoodsSex.getByName(goodsSexCellValue);
        if (goodsSex == null) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("有未识别的性别[" + goodsSexCellValue + "]"));
        }
        String color = MulongUtil.getStringCellValue(colorCell);
        String weightCellValue = MulongUtil.getStringCellValue(weightCell);
        if (StringUtils.isBlank(weightCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写重量的记录"));
        }
        BigDecimal weight = new BigDecimal(weightCellValue);
        String marketPriceCellValue = MulongUtil.getStringCellValue(marketPriceCell);
        if (StringUtils.isBlank(marketPriceCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写吊牌价的记录"));
        }
        BigDecimal marketPrice = new BigDecimal(marketPriceCellValue);
        String saleProps = MulongUtil.getStringCellValue(salePropsCell);
        String material = MulongUtil.getStringCellValue(materialCell);
        String remark = MulongUtil.getStringCellValue(remarkCell);
        UploadProductRecord record = new UploadProductRecord();
        record.setSku(sku);
        record.setBrandValue(brandCellValue);
        record.setBrand(null);
        record.setName(name);
        record.setGoodsCategoryValue(goodsCategoryCellValue);
        record.setGoodsCategory(goodsCategory);
        record.setSeries(series);
        record.setYear(year);
        record.setSeason(season);
        record.setGoodsSexValue(goodsSexCellValue);
        record.setGoodsSex(goodsSex);
        record.setColor(color);
        record.setWeight(weight);
        record.setMarketPrice(marketPrice);
        record.setSaleProps(saleProps);
        record.setMaterial(material);
        record.setRemark(remark);
        return record;
    }

    public void insertProduct(List<UploadProductRecord> records) {
        List<com.mulong.common.domain.pojo.mall.Product> list = records.stream().map(item -> {
            com.mulong.common.domain.pojo.mall.Product e = new com.mulong.common.domain.pojo.mall.Product();
            e.setSku(item.getSku());
            e.setBrandId(item.getBrand().getId());
            e.setName(item.getName());
            e.setGoodsCategory(item.getGoodsCategory().getCode());
            e.setSeries(item.getSeries());
            e.setYear(item.getYear());
            e.setSeason(item.getSeason());
            e.setGoodsSex(item.getGoodsSex().getCode());
            e.setColor(item.getColor());
            e.setWeight(item.getWeight());
            e.setMarketPrice(item.getMarketPrice());
            e.setSaleProps(item.getSaleProps());
            e.setMaterial(item.getMaterial());
            e.setRemark(item.getRemark());
            return e;
        }).collect(Collectors.toList());
        checkProductDuplicateBatch(list, 1000);
        try {
            productDao.addBatch(list, 1000, MallRequestContext.getUsername());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format(e.getMessage()));
        }
    }

    private void checkProductDuplicateBatch(List<com.mulong.common.domain.pojo.mall.Product> records, int batchSize) {
        List<Product> batchList = new ArrayList<>(batchSize);
        for (Product record : records) {
            batchList.add(record);
            if (batchList.size() >= batchSize) {
                checkProductDuplicate(batchList);
                batchList.clear();
            }
        }
        if (!CollectionUtils.isEmpty(batchList)) {
            checkProductDuplicate(batchList);
        }
    }

    private void checkProductDuplicate(List<com.mulong.common.domain.pojo.mall.Product> records) {
        List<Product> result = productDao.queryDuplicate(records, 1);
        if (!CollectionUtils.isEmpty(result)) {
            Product duplicateRecord = result.get(0);
            String sku = duplicateRecord.getSku();
            String msg = String.format("已有商品, 货号[%s]", sku);
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format(msg));
        }
    }

    public void addRow(HSSFRow row, com.mulong.common.domain.pojo.mall.custom.CustomSizeConversion record, HSSFCellStyle defaultStyle) {
        // 品牌
        HSSFCell brandCell = row.createCell(0);
        brandCell.setCellStyle(defaultStyle);
        brandCell.setCellValue(record.getBrandName());
        // 大类
        HSSFCell goodsCategoryCell = row.createCell(1);
        goodsCategoryCell.setCellStyle(defaultStyle);
        GoodsCategory goodsCategory = GoodsCategory.getByCode(record.getGoodsCategory());
        goodsCategoryCell.setCellValue(goodsCategory.getName());
        // 性别
        HSSFCell goodsSexCell = row.createCell(2);
        goodsSexCell.setCellStyle(defaultStyle);
        GoodsSex goodsSex = GoodsSex.getByCode(record.getGoodsSex());
        goodsSexCell.setCellValue(goodsSex.getName());
        // 原尺码
        HSSFCell originalSizeCell = row.createCell(3);
        originalSizeCell.setCellStyle(defaultStyle);
        originalSizeCell.setCellValue(record.getOriginalSize());
        // 转换尺码
        HSSFCell normalizedSizeCell = row.createCell(4);
        normalizedSizeCell.setCellStyle(defaultStyle);
        normalizedSizeCell.setCellValue(record.getNormalizedSize());
        // 尺码备注
        HSSFCell remarkCell = row.createCell(5);
        remarkCell.setCellStyle(defaultStyle);
        remarkCell.setCellValue(record.getRemark());
    }

    public List<UploadSizeConversionRecord> buildUploadSizeConversionRecordList(ExcelReader reader) {
        int rowCount = reader.getRowCount();
        if (rowCount <= 1) {
            log.error("upload file is empty");
            throw new MulongException(ErrorEnums.UPLOAD_FILE_EMPTY);
        }
        List<UploadSizeConversionRecord> uploadRecords = new ArrayList<>(rowCount - 1);
        for (int y = 1; y < reader.getRowCount(); ++y) {
            UploadSizeConversionRecord uploadRecord = buildUploadSizeConversionRecord(reader, y);
            if (uploadRecord != null) {
                uploadRecords.add(uploadRecord);
            }
        }
        List<String> distinctBrandName = uploadRecords.stream().map(UploadSizeConversionRecord::getBrandValue).distinct().collect(Collectors.toList());
        BrandQuery brandQuery = new BrandQuery();
        brandQuery.setNameList(distinctBrandName);
        List<Brand> brands = brandDao.query(brandQuery);
        if (CollectionUtils.isEmpty(brands)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("所有品牌均未识别"));
        }
        Map<String, Brand> brandMap = brands.stream().collect(Collectors.toMap(Brand::getName, Function.identity()));
        for (UploadSizeConversionRecord item : uploadRecords) {
            String brandValue = item.getBrandValue();
            Brand brand = brandMap.get(brandValue);
            if (brand == null) {
                throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("有未识别的品牌[" + brandValue + "]"));
            }
            item.setBrand(brand);
        }
        return uploadRecords;
    }

    private UploadSizeConversionRecord buildUploadSizeConversionRecord(ExcelReader reader, int rowIndex) {
        Cell brandCell = reader.getCell(0, rowIndex);
        Cell goodsCategoryCell = reader.getCell(1, rowIndex);
        Cell goodsSexCell = reader.getCell(2, rowIndex);
        Cell originalSizeCell = reader.getCell(3, rowIndex);
        Cell normalizedSizeCell = reader.getCell(4, rowIndex);
        Cell remarkCell = reader.getCell(5, rowIndex);
        String brandCellValue = MulongUtil.getStringCellValue(brandCell);
        if (StringUtils.isBlank(brandCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写品牌的记录"));
        }
        String goodsCategoryCellValue = MulongUtil.getStringCellValue(goodsCategoryCell);
        GoodsCategory goodsCategory = GoodsCategory.getByName(goodsCategoryCellValue);
        if (goodsCategory == null) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("有未识别的大类[" + goodsCategoryCellValue + "]"));
        }
        String goodsSexCellValue = MulongUtil.getStringCellValue(goodsSexCell);
        GoodsSex goodsSex = GoodsSex.getByName(goodsSexCellValue);
        if (goodsSex == null) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("有未识别的性别[" + goodsSexCellValue + "]"));
        }
        String originalSize = MulongUtil.getStringCellValue(originalSizeCell);
        String normalizedSize = MulongUtil.getStringCellValue(normalizedSizeCell);
        String remark = MulongUtil.getStringCellValue(remarkCell);
        UploadSizeConversionRecord record = new UploadSizeConversionRecord();
        record.setBrandValue(brandCellValue);
        record.setBrand(null);
        record.setGoodsCategoryValue(goodsCategoryCellValue);
        record.setGoodsCategory(goodsCategory);
        record.setGoodsSexValue(goodsSexCellValue);
        record.setGoodsSex(goodsSex);
        record.setOriginalSizeValue(originalSize);
        record.setNormalizedSizeValue(normalizedSize);
        record.setRemarkValue(remark);
        return record;
    }

    public void insertSizeConversion(List<UploadSizeConversionRecord> records) {
        List<com.mulong.common.domain.pojo.mall.SizeConversion> list = records.stream().map(item -> {
            com.mulong.common.domain.pojo.mall.SizeConversion e = new com.mulong.common.domain.pojo.mall.SizeConversion();
            e.setBrandId(item.getBrand().getId());
            e.setGoodsCategory(item.getGoodsCategory().getCode());
            e.setGoodsSex(item.getGoodsSex().getCode());
            e.setOriginalSize(item.getOriginalSizeValue());
            e.setNormalizedSize(item.getNormalizedSizeValue());
            e.setRemark(item.getRemarkValue());
            return e;
        }).collect(Collectors.toList());
        checkDuplicateBatch(list, 1000);
        try {
            sizeConversionDao.addBatch(list, 1000, MallRequestContext.getUsername());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format(e.getMessage()));
        }
    }

    private void checkDuplicateBatch(List<com.mulong.common.domain.pojo.mall.SizeConversion> records, int batchSize) {
        List<SizeConversion> batchList = new ArrayList<>(batchSize);
        for (SizeConversion record : records) {
            batchList.add(record);
            if (batchList.size() >= batchSize) {
                checkDuplicate(batchList);
                batchList.clear();
            }
        }
        if (!CollectionUtils.isEmpty(batchList)) {
            checkDuplicate(batchList);
        }
    }

    private void checkDuplicate(List<com.mulong.common.domain.pojo.mall.SizeConversion> records) {
        List<SizeConversion> result = sizeConversionDao.queryDuplicate(records, 1);
        if (!CollectionUtils.isEmpty(result)) {
            SizeConversion duplicateRecord = result.get(0);
            Brand brand = brandDao.queryById(duplicateRecord.getBrandId());
            String brandName = brand.getName();
            String goodsCategoryName = GoodsCategory.getByCode(duplicateRecord.getGoodsCategory()).getName();
            String goodsSexName = GoodsSex.getByCode(duplicateRecord.getGoodsSex()).getName();
            String originalSize = duplicateRecord.getNormalizedSize();
            String msg = String.format("已有转换规则, 品牌[%s] 大类[%s] 性别[%s] 原尺码[%s]", brandName, goodsCategoryName, goodsSexName, originalSize);
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format(msg));
        }
    }

    public void addRow(HSSFRow row, com.mulong.common.domain.pojo.mall.ProductBarcode record, HSSFCellStyle defaultStyle) {
        // 货号（型号）
        HSSFCell skuCell = row.createCell(0);
        skuCell.setCellStyle(defaultStyle);
        skuCell.setCellValue(record.getSku());
        // 尺码
        HSSFCell normalizedSizeCell = row.createCell(1);
        normalizedSizeCell.setCellStyle(defaultStyle);
        normalizedSizeCell.setCellValue(record.getNormalizedSize());
        // 条形码
        HSSFCell barcodeCell = row.createCell(2);
        barcodeCell.setCellStyle(defaultStyle);
        barcodeCell.setCellValue(record.getBarcode());
    }

    public List<UploadBarcodeRecord> buildUploadBarcodeRecordList(ExcelReader reader) {
        int rowCount = reader.getRowCount();
        if (rowCount <= 1) {
            log.error("upload file is empty");
            throw new MulongException(ErrorEnums.UPLOAD_FILE_EMPTY);
        }
        List<UploadBarcodeRecord> uploadRecords = new ArrayList<>(rowCount - 1);
        for (int y = 1; y < reader.getRowCount(); ++y) {
            UploadBarcodeRecord uploadRecord = buildUploadBarcodeRecord(reader, y);
            if (uploadRecord != null) {
                uploadRecords.add(uploadRecord);
            }
        }
        return uploadRecords;
    }

    private UploadBarcodeRecord buildUploadBarcodeRecord(ExcelReader reader, int rowIndex) {
        Cell skuCell = reader.getCell(0, rowIndex);
        Cell normalizedSizeCell = reader.getCell(1, rowIndex);
        Cell barcodeCell = reader.getCell(2, rowIndex);
        String skuCellValue = MulongUtil.getStringCellValue(skuCell);
        if (StringUtils.isBlank(skuCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写货号的记录"));
        }
        String normalizedSizeCellValue = MulongUtil.getStringCellValue(normalizedSizeCell);
        if (StringUtils.isBlank(normalizedSizeCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写尺码的记录"));
        }
        String barcodeCellValue = MulongUtil.getStringCellValue(barcodeCell);
        if (StringUtils.isBlank(barcodeCellValue)) {
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format("存在没有填写条形码的记录"));
        }
        UploadBarcodeRecord record = new UploadBarcodeRecord();
        record.setSku(skuCellValue);
        record.setNormalizedSize(normalizedSizeCellValue);
        record.setBarcode(barcodeCellValue);
        return record;
    }

    public void insertBarcode(List<UploadBarcodeRecord> records) {
        List<com.mulong.common.domain.pojo.mall.ProductBarcode> list = records.stream().map(item -> {
            com.mulong.common.domain.pojo.mall.ProductBarcode e = new com.mulong.common.domain.pojo.mall.ProductBarcode();
            e.setSku(item.getSku());
            e.setNormalizedSize(item.getNormalizedSize());
            e.setBarcode(item.getBarcode());
            return e;
        }).collect(Collectors.toList());
        checkBarcodeDuplicateBatch(list, 1000);
        try {
            productDao.addBarcodeBatch(list, 1000, MallRequestContext.getUsername());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format(e.getMessage()));
        }
    }

    private void checkBarcodeDuplicateBatch(List<com.mulong.common.domain.pojo.mall.ProductBarcode> records, int batchSize) {
        List<ProductBarcode> batchList = new ArrayList<>(batchSize);
        for (ProductBarcode record : records) {
            batchList.add(record);
            if (batchList.size() >= batchSize) {
                checkBarcodeDuplicate(batchList);
                batchList.clear();
            }
        }
        if (!CollectionUtils.isEmpty(batchList)) {
            checkBarcodeDuplicate(batchList);
        }
    }

    private void checkBarcodeDuplicate(List<com.mulong.common.domain.pojo.mall.ProductBarcode> records) {
        List<ProductBarcode> result = productDao.queryBarcodeDuplicate(records, 1);
        if (!CollectionUtils.isEmpty(result)) {
            ProductBarcode duplicateRecord = result.get(0);
            String sku = duplicateRecord.getSku();
            String normalizedSize = duplicateRecord.getNormalizedSize();
            String barcode = duplicateRecord.getBarcode();
            String msg = String.format("已有条形码, 货号[%s] 尺码[%s] 条形码[%s]", sku, normalizedSize, barcode);
            throw new MulongException(ErrorEnums.HANDLE_FAILED_TEMPLETE.format(msg));
        }
    }

}
